<?php

class AccountsController extends AppController {

    //put your code here
    public $name = "Accounts";

    public function index($contract_id) {
        $this->Session->write('contrato', $contract_id);
        $this->layout = "ajax";
        $this->Account->recursive = -1;
        $this->set('contract_id', $contract_id);
        $this->set('cuentas', $this->Account->find('all', array('conditions' => array('Account.contract_id' => $contract_id), 'order' => 'Account.id DESC', 'fields' => array('Account.fecha_inicio', 'Account.fecha_final', 'Account.contract_id', 'Account.id', 'Account.numero', 'Account.monto', 'Account.observacion', 'Account.estado'))));
    }

    public function print_index($account_id) {
        $this->layout = "ajax";
        $this->set('cuenta', $this->Account->find('first', array('conditions' => array('Account.id' => $account_id), 'fields' => array('Account.fecha_inicio', 'Account.fecha_final', 'Account.id', 'Account.monto'))));
    }

    public function add($contract_id) {
        $this->layout = "ajax";
        $this->Account->User->virtualFields = array(
            'name' => 'CONCAT(User.nombre, " ", User.primer_apellido)'
        );
        $this->set('users', $this->Account->User->find('list', array('fields' => array('User.id', 'User.name'), 'conditions' => array('User.group_id' => array(37, 7)))));
        $this->set('contract_id', $contract_id);
        if (!empty($this->data)) {
            $cuenta = $this->Account->find('first', array('order' => 'Account.numero DESC', 'conditions' => array('Account.contract_id' => $contract_id), 'fields' => array('Account.numero')));
            $this->data['Account']['numero'] = $cuenta['Account']['numero'] + 1;
            if ($this->Account->saveAll($this->data)) {
                $this->Session->setFlash('Datos guardados exitosamente', 'flash_custom');
                $this->redirect(array('controller' => 'Accounts', 'action' => 'index', $this->data['Account']['contract_id']));
            }
        } else {
            $cuenta = $this->Account->find('first', array('order' => 'Account.fecha_final DESC', 'conditions' => array('Account.contract_id' => $contract_id), 'fields' => array('Account.fecha_final')));
            $fecha = $cuenta['Account']['fecha_final'];
            $fecha_inicio = $this->Account->query("SELECT DATE_ADD( '$fecha', INTERVAL 1 DAY ) as Fecha");
            $this->data['Account']['fecha_inicio'] = $fecha_inicio[0][0]['Fecha'];
            $fecha = $fecha_inicio[0][0]['Fecha'];
            $fecha_final = $this->Account->query("SELECT DATE_SUB( DATE_ADD( '$fecha', INTERVAL 1 MONTH ) , INTERVAL 1 DAY )as Fecha");
            $this->data['Account']['fecha_final'] = $fecha_final[0][0]['Fecha'];
        }
    }

    public function edit($id) {
        $this->layout = "ajax";
        $this->Account->User->virtualFields = array(
            'name' => 'CONCAT(User.nombre, " ", User.primer_apellido)'
        );
        $this->set('users', $this->Account->User->find('list', array('fields' => array('User.id', 'User.name'), 'conditions' => array('User.group_id' => array(37, 7)))));

        if (empty($this->data)) {
            $this->Account->recursive = -1;
            $this->data = $this->Account->find('first', array('conditions' => array('Account.id' => $id)));
        } else {
            if ($this->Account->saveAll($this->data)) {
                $this->Session->setFlash('Datos editados exitosamente', 'flash_custom');
                $this->redirect(array('controller' => 'Accounts', 'action' => 'index', $this->data['Account']['contract_id']));
            }
        }
    }

    public function print_letter($id) {
        Configure::write('debug', 0);
        $this->layout = 'pdf';
        //$this->response->header(array('Content-type: application/pdf'));
        $this->Account->recursive = -1;

        $options['joins'] = array(
            array('table' => 'contracts',
                'alias' => 'Contract',
                'type' => 'Left',
                'conditions' => array(
                    'Contract.id = Account.contract_id'
                )
            ),
            array('table' => 'users',
                'alias' => 'User',
                'type' => 'Left',
                'conditions' => array(
                    'Contract.user_id = User.id'
                )
            )
        );

        $options['conditions'] = array(
            'Account.id' => $id
        );
        $options['fields'] = array(
            'Account.fecha_inicio',
            'Account.fecha_final',
            'Account.monto',
            'Account.fecha_cuenta',
            'User.nombre',
            'User.primer_apellido',
            'User.segundo_apellido',
            'User.cedula',
            'Contract.numero',
            'Contract.fecha'
        );


        $cuenta = $this->Account->find('first', $options);
        $this->set('cuenta', $cuenta);
    }

    public function print_certification($id) {

        $this->layout = 'pdf';
        $this->Account->recursive = -1;
        //$this->response->header(array('Content-type: application/pdf'));
        $options['joins'] = array(
            array('table' => 'contracts',
                'alias' => 'Contract',
                'type' => 'Left',
                'conditions' => array(
                    'Contract.id = Account.contract_id'
                )
            ),
            array('table' => 'users',
                'alias' => 'User',
                'type' => 'Left',
                'conditions' => array(
                    'Contract.user_id = User.id'
                )
            )
        );

        $options['conditions'] = array(
            'Account.id' => $id
        );
        $options['fields'] = array(
            'Account.fecha_inicio',
            'Account.fecha_final',
            'Account.numero',
            'Account.monto',
            'Account.fecha_cuenta',
            'User.nombre',
            'User.primer_apellido',
            'User.segundo_apellido',
            'User.cedula',
            'Contract.*',
        );


        $cuenta = $this->Account->find('first', $options);
        $this->set('cuenta', $cuenta);
        $sm = 0;
        $suma = $this->Account->find('first', array('conditions' => array('AND' => array('Account.contract_id' => $cuenta['Contract']['id'], 'Account.numero <' => $cuenta['Account']['numero'])), 'fields' => array('SUM(Account.monto)AS suma')));

        if (is_null($suma[0]['suma']))
            $sm = 0;
        else
            $sm = $suma[0]['suma'];
        $this->set('suma', $sm);
    }

    public function edit_retention($id, $opcion) {
        $this->layout = "ajax";
        $this->set('opcion', $opcion);
        if (empty($this->data)) {
            $this->Account->recursive = -1;
            $this->data = $this->Account->find('first', array(
                'conditions' => array('Account.id' => $id),
                'fields' => array('Account.formato_retencion', 'Account.fecha_cuenta', 'Account.fecha_inicio', 'Account.id', 'Account.contract_id', 'Account.numero_planilla'),
            ));
            $this->data['Account']['formato_retencion'] = $this->data['Account']['formato_retencion'];
            if ($this->data['Account']['formato_retencion'] == "") {
                $this->Account->Contract->recursive = -1;
                $contrato = $this->Account->Contract->find(
                        'first', array(
                    'conditions' => array('Contract.id' => $this->data['Account']['contract_id']),
                    'fields' => array('Contract.fecha', 'Contract.numero', 'Contract.fecha', 'User.nombre', 'User.primer_apellido', 'User.cedula'),
                    'joins' => array(
                        array('table' => 'users',
                            'alias' => 'User',
                            'type' => 'Left',
                            'conditions' => array(
                                'Contract.user_id = User.id'
                            )
                        )
                    )
                        )
                );
                $numero = $contrato['Contract']['numero'];
                $nombre = $contrato['User']['nombre'] . " " . $contrato['User']['primer_apellido'];
                $cedula = $contrato['User']['cedula'];

                App::import('Vendor', 'EnLetras', array('file' => 'EnLetras.class.php'));
                $V = new EnLetras();
                $mes = $V->obtenerMes($this->data['Account']['fecha_cuenta']);
                $mesc = $V->obtenerMes($this->data['Account']['fecha_inicio']);
                $mesc = explode(" ", $mesc);
                $anio = $mesc[3];
                $mesc = $mesc[0];

                $mescontrato = $V->obtenerMes($contrato['Contract']['fecha']);
                $str = "Bogotá $mes <br>Señores<br>INCODER<br>Bogotá<br><br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;&nbspAsunto: Solicitud disminución base de retención en la fuente e ICA.<br><br><div style=\"text-align:justify\">A través de la presente y en cumplimiento de lo dispuesto en el artículo 126-1 del Estatuto Tributario, el artículo 50 de la Ley 789 de 2002, el artículo 18 de la Ley 1122 de 2007, el artículo 15 del Decreto 836 de 1991 y el parágrafo 1 del artículo 4 del decreto 2271 de 2009, le solicito se efectúe la correspondiente reducción en la base de retenciones por concepto del pago de los aportes al Sistema General de Seguridad Social realizado para el periodo del mes de $mesc del año $anio en cumplimiento del Contrato $numero &nbsp; de&nbsp; $mescontrato.   ";
                $str.="<br><br>Así mismo, les solicito la disminución de la base de retención del ICA conforme al estatuto tributario municipal o local (cuando aplique).<br><br>Los pagos correspondientes al periodo de ejecución del contrato del mes de $mesc &nbsp; se efectuaron a través de la planilla número " . $this->data['Account']['numero_planilla'] . " como se relaciona a continuación:  <br> ";
                $str.="<br><table style=\"border:1px solid;width:782px;height:133px\" rules=\"all\"><tbody ><tr><td style=\"text-align:center;width:30%\">&nbsp;Concepto</td><td style=\"text-align:center;width:25%\">&nbsp;Administradora</td><td style=\"text-align:center;width:20%\">Periodo <br></td><td style=\"text-align:center;width:25%\">&nbsp;Valor<br>(sin intereses de mora)  </td></tr><tr style=\"text-align:left\" padding=\"5\"><td>&nbsp;Aporte obligatorio salud</td><td>&nbsp;</td><td>&nbsp;</td><td style=\"text-align:center\" >&nbsp;</td></tr><tr style=\"text-align:left\" padding=\"5\"><td>&nbsp;Aporte obligatorio pensión</td><td>&nbsp;</td><td>&nbsp;</td><td style=\"text-align:center\">&nbsp;</td></tr><tr style=\"text-align:left\" padding=\"5\"><td>&nbsp;AFC</td><td>&nbsp;</td><td>&nbsp;</td><td style=\"text-align:center\">&nbsp;</td></tr><tr style=\"text-align:left\" padding=\"5\"><td>&nbsp;ARL</td><td>&nbsp;</td><td>&nbsp;</td><td style=\"text-align:center\">&nbsp;</td></tr><tr style=\"text-align:left\" padding=\"5\"><td>&nbsp;Aportes voluntarios pensión</td><td>&nbsp;</td><td>&nbsp;</td><td style=\"text-align:center\">&nbsp;</td></tr><tr style=\"text-align:center\" padding=\"5\"><td></td><td colspan=\"2\">&nbsp;TOTAL A DESCONTAR RETEFUENTE (debe ser menor o igual al 30% del ingreso por la ejecución del periodo del contrato que se está tramitando para pago  &nbsp;<br></td><td style=\"text-align:center\">&nbsp;</td></tr></tbody></table><br>Cordial saludo,<br><br><br>____________________<br>$nombre<br>C.C $cedula<br><br>Anexo: Soportes Pago Sistema General de Seguridad Social  <br></div>  ";
                $this->data['Account']['formato_retencion'] = $str;
            }
        } else {
            if ($this->Account->saveAll($this->data)) {
                $this->Session->setFlash('Datos editados exitosamente', 'flash_custom');
                if ($opcion == 1) {
                    $this->redirect(array('controller' => 'Accounts', 'action' => 'index', $this->data['Account']['contract_id']));
                } else {
                    $this->redirect(array('controller' => 'Accounts', 'action' => 'review', $this->data['Account']['id']));
                }
            }
        }
    }

    public function print_retention($id) {
        $this->layout = 'pdf';
        $this->Account->recursive = -1;
        //$this->response->header(array('Content-type: application/pdf'));
        $this->set('cuenta', $this->Account->find('first', array('conditions' => array('Account.id' => $id), 'fields' => array('Account.formato_retencion', 'Account.id', 'Account.contract_id'))));
    }

    public function edit_contract_certification($id, $opcion) {
        $this->layout = "ajax";
        $this->set('opcion', $opcion);
        if (empty($this->data)) {
            $this->Account->recursive = -1;
            $this->data = $this->Account->find('first', array('conditions' => array('Account.id' => $id), 'fields' => array('Account.formato_certificacion', 'Account.id', 'Account.monto', 'Account.contract_id', 'Account.fecha_cuenta')));
            $this->data['Account']['formato_certificacion'] = $this->data['Account']['formato_certificacion'];

            if ($this->data['Account']['formato_certificacion'] == "") {

                App::Import('Model', 'Contract');
                $con = new Contract();
                $con->recursive = -1;
                $options['joins'] = array(
                    array('table' => 'users',
                        'alias' => 'User',
                        'type' => 'Left',
                        'conditions' => array(
                            'Contract.user_id = User.id'
                        )
                    ),
                    array('table' => 'branches',
                        'alias' => 'Branch',
                        'type' => 'Left',
                        'conditions' => array(
                            'User.branch_id = Branch.id'
                        )
                    )
                );
                $options['conditions'] = array('Contract.id' => $this->data['Account']['contract_id']);
                $options['fields'] = array('User.nombre', 'User.primer_apellido', 'User.segundo_apellido', 'Contract.numero', 'Contract.fecha_inicio', 'Contract.fecha_final', 'Contract.monto', 'Branch.nombre');
                $contrato = $con->find('first', $options);
                $nombre = $contrato['User']['nombre'] . " " . $contrato['User']['primer_apellido'] . " " . $contrato['User']['segundo_apellido'];
                $numc = $contrato['Contract']['numero'];
                $fec1 = $contrato['Contract']['fecha_inicio'];
                $fec2 = $contrato['Contract']['fecha_final'];
                $monto = number_format($contrato['Contract']['monto'], 0, ',', '.');
                App::import('Vendor', 'EnLetras', array('file' => 'EnLetras.class.php'));
                $V = new EnLetras();
                $mes = $V->obtenerMes($this->data['Account']['fecha_cuenta']);
                $montocuenta = number_format($this->data['Account']['monto'], 0, ',', '.');
                $str = "<div style=\"text-align:justify\"><br>Certifico bajo la gravedad de juramento, la aplicación del artículo 1 del decreto 3590 de 2011 reglamentario del artículo 173 ley 1450 de 2011, Que pertenezco al régimen  simplificado del impuesto sobre las ventas, y el (los) contrato (s) de prestación de servicios que originan pagos a mi favor en el mes de XXX del año 201X son los que se relacionan a continuación:</div>";
                $str.="<br><br><table style=\"border:1px solid;width:100%;height:130px\" rules=\"all\"><tbody><tr style=\"background-color:#cccccc\"><td style=\"text-align:center;font-size:x-small\">&nbsp;NIT Entidad Contratante  </td><td style=\"text-align:center;font-size:x-small\">&nbsp;Razón social entidad</td><td style=\"text-align:center;font-size:x-small\">&nbsp;N° del Contrato  </td><td style=\"text-align:center;font-size:x-small\">&nbsp;Fecha de Inicio del Contrato  </td><td style=\"text-align:center;font-size:x-small\">Fecha de terminación&nbsp; Contrato  &nbsp;</td><td style=\"text-align:center;font-size:x-small\">Valor Total Contrato  &nbsp;</td><td style=\"text-align:center;font-size:x-small\">Valor Mensual Pago<br>A  &nbsp;</td><td style=\"text-align:center;font-size:x-small\">&nbsp;Valor IVA (régimen Común)<br>B  </td><td style=\"text-align:center;font-size:x-small\">&nbsp;Valor por Pagar en el Mes<br>A + B  </td></tr><tr style=\"text-align:center;font-size:x-small\"><td>830.122.398-0</td><td>INCODER</td><td>$numc</td><td>$fec1</td><td>$fec2</td><td> $ $monto</td><td>$ $montocuenta</td><td>$0</td><td>$ $montocuenta</td></tr><tr><td>&nbsp;</td><td>&nbsp;</td><td>&nbsp;</td><td>&nbsp;</td><td>&nbsp;</td><td>&nbsp;</td><td>&nbsp;</td><td>&nbsp;</td><td>&nbsp;</td></tr><tr><td>&nbsp;</td><td>&nbsp;</td><td>&nbsp;</td><td>&nbsp;</td><td>&nbsp;</td><td>&nbsp;</td><td>&nbsp;</td><td>&nbsp;</td><td>&nbsp;</td></tr><tr><td>&nbsp;</td><td>&nbsp;</td><td>&nbsp;</td><td>&nbsp;</td><td>&nbsp;</td><td>&nbsp;</td><td>&nbsp;</td><td>&nbsp;</td><td>&nbsp;</td></tr><tr ><td style=\"text-align:left\" colspan=\"8\">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<strong>TOTAL INGRESOS EN EL MES</strong>  <br></td><td style=\"text-align:center;font-size:9\">$ $montocuenta</td></tr></tbody></table> ";
                $this->data['Account']['formato_certificacion'] = $str;
            }
        } else {

            if ($this->Account->saveAll($this->data)) {
                $this->Session->setFlash('Datos editados exitosamente', 'flash_custom');
                if ($opcion == 1) {
                    $this->redirect(array('controller' => 'Accounts', 'action' => 'index', $this->data['Account']['contract_id']));
                } else {
                    $this->redirect(array('controller' => 'Accounts', 'action' => 'review', $this->data['Account']['id']));
                }
            }
        }
    }

    public function print_contract_certification($id) {
        $this->layout = 'pdf';
        $this->Account->recursive = -1;
        // $this->le->header(array('Content-type: application/pdf'));
        $options['joins'] = array(
            array('table' => 'contracts',
                'alias' => 'Contract',
                'type' => 'Left',
                'conditions' => array(
                    'Contract.id = Account.contract_id'
                )
            ),
            array('table' => 'users',
                'alias' => 'User',
                'type' => 'Left',
                'conditions' => array(
                    'Contract.user_id = User.id'
                )
            )
        );

        $options['conditions'] = array(
            'Account.id' => $id
        );
        $options['fields'] = array(
            'Account.fecha_inicio',
            'Account.fecha_final',
            'Account.numero',
            'Account.monto',
            'Account.fecha_cuenta',
            'Account.formato_certificacion',
            'User.nombre',
            'User.primer_apellido',
            'User.segundo_apellido',
            'User.cedula',
            'Contract.*',
        );


        $cuenta = $this->Account->find('first', $options);
        $this->set('cuenta', $cuenta);
        $sm = 0;
        $suma = $this->Account->find('first', array('conditions' => array('AND' => array('Account.contract_id' => $cuenta['Contract']['id'], 'Account.numero <' => $cuenta['Account']['numero'])), 'fields' => array('SUM(Account.monto)AS suma')));

        if (is_null($suma[0]['suma']))
            $sm = 0;
        else
            $sm = $suma[0]['suma'];
        $this->set('suma', $sm);
    }

    public function change_state($account_id, $contract_id) {
        $datos = array('Account' => array(
                'id' => $account_id,
                'estado' => "Revisión",
        ));
        if ($this->Account->save($datos)) {
            $this->Session->setFlash('su cuenta de cobro está en proceso de revisión.', 'flash_custom');
            $this->redirect(array('controller' => 'Accounts', 'action' => 'index', $contract_id));
        }
    }

    public function review($account_id) {
        if (!empty($this->data)) {
            if ($this->Account->saveAll($this->data)) {
                $this->Session->setFlash('Datos editados exitosamente', 'flash_custom');
                $this->redirect(array('controller' => 'Accounts', 'action' => 'review_index'));
            }
        } else {
            $this->data = $this->Account->find('first', array('conditions' => array('Account.id' => $account_id)));
        }
    }

    public function review_index() {

        $this->layout = "ajax";
        $this->Account->recursive = -1;

        $this->set('cuentas', $this->Account->find('all', array('conditions' => array('Account.user_id' => $this->Auth->user('id'), 'Account.estado' => "Revisión"), 'order' => array('Account.id ASC'), 'fields' => array('Account.fecha_inicio', 'User.nombre', 'User.primer_apellido', 'User.segundo_apellido', 'Account.fecha_final', 'Account.contract_id', 'Account.id', 'Account.numero', 'Account.monto', 'Contract.numero'), 'joins' => array(array('table' => 'contracts', 'alias' => 'Contract', 'type' => 'left', 'conditions' => 'Contract.id=Account.contract_id'), array('table' => 'users', 'alias' => 'User', 'type' => 'left', 'conditions' => 'Contract.user_id=User.id')))));
        $this->set('id_usuario', $this->Auth->user('id'));
    }

    function _CleanNonACIIchar($text) {
        $input = '/[^(\x20-\x7F)]*/'; //el rango de caracteres que no queremos
        $Text_Output = preg_replace($input, '', $text);
        return $Text_Output;
    }

}

?>
